package dados;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import org.apache.jasper.tagplugins.jstl.core.When;

import com.sun.jndi.url.dns.dnsURLContext;

import negocio.*;
import excecoes.*;

public class RepositorioDisciplinas implements IRepositorioDisciplinas {
	private static final String QUERY_INSERT = "insert into disciplina(nome,matricula_professor,carga_horaria,ultima_atualizacao) values(?,?,?,now());";
	private static final String QUERY_UPDATE = "update disciplina set nome=?, matricula_professor=?, carga_horaria=? ultima_atualizacao=now() where codigo=?;";
	private static final String QUERY_DELETE = "delete from disciplina where codigo=?;";
	private static final String QUERY_SELECT = "select * from disciplina where ? like ?%;";
	private static final String QUERY_SELECT_ALUNO_DISCIPLINA = "select aluno.matricula as matricula, pessoa.nome as nome, pessoa.idade as idade, aluno.curso as curso, aluno.rendimento as rendimento, aluno.trancada as trancada, aluno.ultima_atualizacao as atualizacao from disciplina inner join aluno_disciplina on (disciplina.codigo = aluno_disciplina.codigo) inner join aluno on (aluno_disciplina.matricula = aluno.matricula) inner join pessoa on (aluno.matricula = pessoa.matricula) where disciplina.codigo = ? order by pessoa.nome asc;";

	private String url = "jdbc:mysql://localhost/escola";
	private String user = "root";
	private String senha = "";

	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

	}

	private Connection obterConexao() {

		try {
			Connection con = DriverManager.getConnection(this.url, this.user,
					this.senha);
			return con;
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}
		return null;

	}

	public Vector<Disciplina> PesquisarPorProfessor(Disciplina d) {
		Connection con = obterConexao();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Vector<Disciplina> listaDisciplinas = new Vector<Disciplina>();
		try {
			pstm = con.prepareStatement(QUERY_SELECT);
			pstm.setString(1, "matricula_professor");
			pstm.setInt(2, d.getProfessor().getMatricula());
			rs = pstm.executeQuery();

			while (rs.next()) {
				try {
					Disciplina disc = this.consultaDisciplinaPorCodigo(rs
							.getInt("codigo"));
					listaDisciplinas.add(disc);
				} catch (DisciplinaNaoCadastradaException e) {
					e.printStackTrace();
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listaDisciplinas;

	}

	public void alterarDisciplina(Disciplina d)
			throws DisciplinaNaoCadastradaException {
		Connection con = obterConexao();
		PreparedStatement ps = null;
		Statement stm = null;
		try {
			con.setAutoCommit(false);
			stm = con.createStatement();
			ps = con.prepareStatement(QUERY_UPDATE);
			ps.setString(1, d.getNomeDisciplina());
			ps.setInt(2, d.getProfessor().getMatricula());
			ps.setInt(3, d.getCargaHoraria());
			ps.setInt(4, Integer.valueOf(d.getCodigo()));
			ps.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
				stm.close();
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
				ex.printStackTrace();
			}
		}
	}

	public Disciplina consultaDisciplinaPorCodigo(int cod)
			throws DisciplinaNaoCadastradaException {
		Connection con = obterConexao();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		Disciplina disc = new Disciplina();
		RepositorioPessoaBD rp = null;
		try {
			pstm = con.prepareStatement(QUERY_SELECT);
			pstm.setString(1, "codigo");
			pstm.setInt(2, cod);
			rs = pstm.executeQuery();
			disc.setCodigo(String.valueOf(cod));
			disc.setNomeDisciplina(rs.getString("nome"));
			disc.setCargaHoraria(rs.getInt("carga_horaria"));
			Professor prof = (Professor) rp.consultarPessoaPorMatricula(rs
					.getInt("matricula_professor"));
			disc.setProfessor(prof);
			disc.setUltimaAtualizacao(rs.getInt("ultima_atualizacao"));

			// Carregando a lista de alunos da disciplina...
			PreparedStatement ps2 = con
					.prepareStatement(QUERY_SELECT_ALUNO_DISCIPLINA);
			ps2.setInt(1, rs.getInt("codigo"));
			rs2 = ps2.executeQuery();
			Vector<Aluno> listaAlunos = new Vector<Aluno>();
			while (rs2.next()) {
				Aluno a = new Aluno();
				a.setMatricula(rs2.getInt("matricula"));
				a.setNome(rs2.getString("nome"));
				a.setIdade(rs2.getInt("idade"));
				a.setCurso(rs2.getInt("curso"));
				a.setCoeficienteRendimento(rs2.getInt("rendimento"));
				if (rs2.getString("trancada").equals("1")) {
					a.setMatriculaTrancada(true);
				} else {
					a.setMatriculaTrancada(false);
				}
				a.setUltimaAtualizacao(rs2.getDate("atualizacao"));
				listaAlunos.add(a);
			}

			disc.setAlunos(listaAlunos);
			return disc;
		} catch (SQLException e) {

			e.printStackTrace();
		} catch (PessoaNaoCadastradaException e) {

			e.printStackTrace();
		} finally {
			try {
				con.close();
				pstm.close();
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
				ex.printStackTrace();
			}
		}
		return null;
	}

	public Vector<Disciplina> consultarDisciplinaPorNome(String nome) {
		Connection con = obterConexao();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Vector<Disciplina> listaDisciplinas = new Vector<Disciplina>();
		try {
			pstm = con.prepareStatement(QUERY_SELECT);
			pstm.setString(1, "nome");
			pstm.setString(2, nome);
			rs = pstm.executeQuery();

			while (rs.next()) {
				try {
					Disciplina disc = this.consultaDisciplinaPorCodigo(rs
							.getInt("codigo"));
					listaDisciplinas.add(disc);
				} catch (DisciplinaNaoCadastradaException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listaDisciplinas;
	}

	public void inserirDisciplina(Disciplina d)
			throws DisciplinaComMesmoNomeException {
		Connection con = obterConexao();
		PreparedStatement ps = null;
		Statement stm = null;
		try {
			con.setAutoCommit(false);
			stm = con.createStatement();
			ps = con.prepareStatement(QUERY_INSERT);
			ps.setString(1, d.getNomeDisciplina());
			ps.setInt(2, d.getProfessor().getMatricula());
			ps.setInt(3, d.getCargaHoraria());
			ps.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
				stm.close();
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
				ex.printStackTrace();
			}
		}
	}

	public void removerDisciplina(Disciplina d)
			throws DisciplinaNaoCadastradaException {
		Connection con = obterConexao();
		PreparedStatement ps = null;
		Statement stm = null;
		try {
			con.setAutoCommit(false);
			stm = con.createStatement();
			ps = con.prepareStatement(QUERY_DELETE);
			ps.setString(1, d.getCodigo());
			ps.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				con.close();
				ps.close();
				stm.close();
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
				ex.printStackTrace();
			}
		}

	}

}
